﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using SystemSettings;
using Labthink.PCSoft.UniversalHost4.Model.Setting;
using Labthink.PCSoft.UniversalHost4.Model.User;
using Labthink.PCSoft.UniversalHost4.Utility;
using NLog;

namespace Labthink.PCSoft.UniversalHost4.DataAccess
{
    /// <summary>
    /// 角色和权限
    /// </summary>
    public class RoleAndPerRepository : BaseRepository
    {
        private static readonly Logger Log = LogManager.GetCurrentClassLogger();

        /// <summary>
        /// 添加角色
        /// </summary>
        /// <param name="role"></param>
        /// <returns></returns>
        public static int AddRole(Role role)
        {
            IsDbExist();
            Log.Debug("添加一个角色：{0}", role.Name);
            try
            {
                return SqliteHelper.ExecuteNonQuery(DbFile, "INSERT INTO Role (Name,IsShow) VALUES (@Name,1)", role.Name);
            }
            catch (Exception e)
            {
                Log.Error("添加角色失败:{0}", e, "INSERT INTO Role (Name,IsShow) VALUES (@Name,1)");
                return -1;
            }
        }

        /// <summary>
        /// 添加角色
        /// </summary>
        /// <param name="role"></param>
        /// <returns></returns>
        public static int AddRole(RoleInfoSimple role)
        {
            IsDbExist();
            if (Exists(role.RoleName))
            {
                var error = LanguageManager.GetLanguageValue("NameExist_RoleName", role.RoleName);
                throw new BusinessException(error);
            }

            Log.Debug("添加一个角色：{0}", role.RoleName);
            try
            {
                var rid = SqliteHelper.ExecuteScalar(DbFile, "INSERT INTO Role (Name,IsShow) VALUES (@Name,1);select last_insert_rowid()", role.RoleName).ToInt32();
                if (rid > 0)
                {
                    using var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
                    con.Open();
                    var tran = con.BeginTransaction();
                    var sql = "INSERT INTO RolePermission (Rid,PCode) Values (@Rid, @PCode)";
                    foreach (var code in role.Pers)
                    {
                        SqliteHelper.ExecuteNonQuery(tran, sql, rid, code);
                    }

                    try
                    {
                        tran.Commit();
                        return role.Pers.Count;
                    }
                    catch (Exception e)
                    {
                        tran.Rollback();
                        SqliteHelper.ExecuteNonQuery(DbFile, "DELETE FROM Role WHERE Id=" + rid);
                        Log.Error("添加角色对应权限失败:{0}", e, "INSERT INTO  RolePermission (Rid,PCode) Values (@Rid, @PCode)");
                        var error = LanguageManager.GetLanguageValue("ActionFail");
                        throw new BusinessException(error);
                    }
                }

                var error1 = LanguageManager.GetLanguageValue("ActionFail");
                throw new BusinessException(error1);
            }
            catch (Exception e)
            {
                Log.Error("添加角色失败:{0}", e, "INSERT INTO Role (Name,IsShow) VALUES (@Name,1)");
                var error = LanguageManager.GetLanguageValue("ActionFail");
                throw new BusinessException(error);
            }
        }

        /// <summary>
        /// 修改角色名称
        /// </summary>
        /// <param name="role"></param>
        /// <returns></returns>
        public static int UpdateRole(Role role)
        {
            IsDbExist();
            Log.Debug("修改角色{0}的名称", role.Id);
            try
            {
                return SqliteHelper.ExecuteNonQuery(DbFile, "UPDATE Role SET Name=@Name WHERE Id=@Id", role.Name, role.Id);
            }
            catch (Exception e)
            {
                Log.Error("修改角色失败:{0}", e, "UPDATE Role SET Name=@Name WHERE Id=@Id");
                return -1;
            }
        }

        /// <summary>
        /// 获取所有角色
        /// </summary>
        /// <returns></returns>
        public static List<Role> GetRoles(bool includeHidden = false)
        {
            IsDbExist();
            Log.Debug("获取全部角色信息");
            var roles = new List<Role>();
            try
            {
                var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
                con.Open();
                using SQLiteCommand cmd = con.CreateCommand();
                var sql = "SELECT * FROM Role WHERE IsShow = 1 ORDER BY Id DESC ";
                if (includeHidden)
                {
                    sql = "SELECT * FROM Role ORDER BY Id DESC ";
                }

                var reader = SqliteHelper.ExecuteReader(cmd, sql, null);
                while (reader.Read())
                {
                    roles.Add(new Role
                    {
                        Id = reader["Id"].ToInt32(),
                        Name = reader["Name"].ToString(),
                        IsShow = reader["IsShow"].ToBool()
                    });
                }
                reader.Close();
            }
            catch (Exception e)
            {
                Log.Error("查询所有角色出错:{0}", e, "SELECT * FROM Role WHERE IsShow=1");
            }

            return roles;
        }

        /// <summary>
        /// 根据角色id获取信息
        /// </summary>
        /// <param name="rid"></param>
        /// <returns></returns>
        public static RoleInfo GetRole(int rid)
        {
            Log.Debug("根据id {0} 获取角色信息", rid);

            var ds =
                SqliteHelper.ExecuteDataSet(DbFile, "SELECT * FROM Role WHERE  Id=@Id", new object[] { rid });
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            var role = new RoleInfo
            {
                RoleId = ds.Tables[0].Rows[0]["id"].ToInt32(),
                RoleName = ds.Tables[0].Rows[0]["Name"].ToString(),
                Pers = new List<PerInfo>()
            };

            var rps = new List<RolePermission>();
            try
            {
                var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
                con.Open();
                using SQLiteCommand cmd = con.CreateCommand();
                IDataReader reader = SqliteHelper.ExecuteReader(cmd, "SELECT * FROM RolePermission WHERE Rid=@Rid", new object[] { role.RoleId });
                while (reader.Read())
                {
                    rps.Add(new RolePermission
                    {
                        Id = reader["Id"].ToInt32(),
                        PCode = reader["PCode"].ToString(),
                        RoleId = reader["Rid"].ToInt32()
                    });
                }
                reader.Close();
            }
            catch (Exception e)
            {
                Log.Error("查询角色对应得权限出错:{0}", e, "SELECT * FROM RolePermission WHERE Rid=@Rid");
            }

            List<Permission> pers = GetAllPermissions();
            foreach (Permission per in pers)
            {
                var p = new PerInfo { Code = per.Code, Parentid = per.ParentId, Pid = per.Id.ToString(), Type = per.Type, Name = per.NameCn };
                if (rps.Any(r => r.PCode == per.Code)) p.Checked = true;
                role.Pers.Add(p);
            }
            return role;
        }

        /// <summary>
        /// 处理角色权限对应关系
        /// </summary>
        /// <param name="role"></param>
        /// <returns></returns>
        public static int MergeRole(RoleInfoSimple role)
        {
            IsDbExist();
            Log.Debug("修改角色{0}的操作权限", role.RoleName);
            using var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
            con.Open();
            var cmdD = con.CreateCommand();
            cmdD.CommandText = "DELETE FROM  RolePermission WHERE Rid=@Rid";
            cmdD.Parameters.Add(new SQLiteParameter("@Rid", role.RoleId));
            var cmds = new List<SQLiteCommand>();
            const string sql = "INSERT INTO RolePermission (Rid,PCode) VALUES (@Rid,@PCode)";
            foreach (string code in role.Pers)
            {
                var cmd = con.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.Add(new SQLiteParameter("@Rid", role.RoleId));
                cmd.Parameters.Add(new SQLiteParameter("@PCode", code));
                cmds.Add(cmd);
            }

            SQLiteTransaction tran = con.BeginTransaction();
            try
            {
                SqliteHelper.ExecuteNonQuery(tran, "UPDATE Role SET Name=@Name WHERE Id=@Id", role.RoleName, role.RoleId);
                SqliteHelper.ExecuteNonQuery(tran, cmdD);
                foreach (var c in cmds)
                {
                    SqliteHelper.ExecuteNonQuery(tran, c);
                }
                tran.Commit();
                return role.Pers.Count;
            }
            catch (Exception e)
            {
                Log.Error("更新权限角色对应关系失败:{0} / {1}", e, cmdD.CommandText, "INSERT INTO RolePermission(Rid,PCode) VALUES (@Rid,@PCode)");
                tran.Rollback();
                var error = LanguageManager.GetLanguageValue("ActionFail");
                throw new BusinessException(error);
            }
        }

        /// <summary>
        /// 根据用户获取权限
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public static List<UserPermission> GetPermissionsByUid(int uid)
        {
            IsDbExist();
            Log.Debug("获取用户{0}的全部权限", uid);

            var users = new List<UserPermission>();
            try
            {
                var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
                con.Open();
                using SQLiteCommand cmd = con.CreateCommand();
                IDataReader reader = SqliteHelper.ExecuteReader(cmd, "SELECT * FROM ViewUserPermission WHERE Id=@Id", new object[] { uid });
                while (reader.Read())
                {
                    users.Add(new UserPermission
                    {
                        Id = reader["Id"].ToInt32(),
                        Code = reader["Code"].ToString(),
                        Type = reader["Type"].ToInt32(),
                        Pid = reader["Pid"].ToString()
                    });
                }
                reader.Close();
            }
            catch (Exception e)
            {
                Log.Error("获取用户权限出错:{0}", e, "SELECT * FROM ViewUserPermission WHERE Id=@Id");
            }

            return users;
        }

        /// <summary>
        /// 添加一条需要追踪的参数
        /// </summary>
        /// <param name="dataNo"></param>
        /// <returns></returns>
        public static int InsertPermissionForParamTrace(int dataNo)
        {
            IsDbExist();

            var o = SqliteHelper.ExecuteScalar(DbFile, "SELECT COUNT(1) FROM Permission WHERE Code=@Code", dataNo);
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile)))
            {
                con.Open();
                var tran = con.BeginTransaction();
                SqliteHelper.ExecuteNonQuery(tran, "UPDATE ParamForTrace SET IsTrace=1 WHERE DataNo=@DataNo", dataNo);
                if (o == null || o.ToInt32() == 0)
                {
                    SqliteHelper.ExecuteNonQuery(tran, "INSERT INTO  Permission (Code,ParentId,Type,IsNative) VALUES (@Code,'ParamSet',1,0)", dataNo);
                }
                try
                {
                    tran.Commit();
                    return 1;
                }
                catch (Exception e)
                {
                    Log.Error(e);
                    tran.Rollback();
                    return -1;
                }
            }
        }

        /// <summary>
        /// 移除一条需要追踪的参数
        /// </summary>
        /// <param name="dataNo"></param>
        /// <returns></returns>
        public static int RemovePermissionForParamTrace(int dataNo)
        {
            IsDbExist();
            var o = SqliteHelper.ExecuteScalar(DbFile, "SELECT COUNT(1) FROM Permission WHERE Code=@Code", dataNo);
            using var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
            con.Open();
            var tran = con.BeginTransaction();
            SqliteHelper.ExecuteNonQuery(tran, "UPDATE ParamForTrace SET IsTrace=0 WHERE DataNo=@DataNo", dataNo);
            if (o != null && o.ToInt32() > 0)
            {
                SqliteHelper.ExecuteNonQuery(tran, "DELETE FROM  Permission WHERE Code=@code", dataNo);
            }
            try
            {
                tran.Commit();
                return 1;
            }
            catch (Exception e)
            {
                Log.Error(e);
                tran.Rollback();
                return -1;
            }
        }

        public static List<ParamForTrace> GetParamsForTrace()
        {
            IsDbExist();
            Log.Debug("获取全部可追踪的参数");
            var pts = new List<ParamForTrace>();
            try
            {
                var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
                con.Open();
                using SQLiteCommand cmd = con.CreateCommand();
                IDataReader reader = SqliteHelper.ExecuteReader(cmd, "SELECT * FROM ParamForTrace ", null);
                while (reader.Read())
                {
                    pts.Add(new ParamForTrace
                    {
                        DataNo = reader["DataNo"].ToInt32(),
                        IsTrace = reader["IsTrace"].ToInt32()
                    });
                }
                reader.Close();
            }
            catch (Exception e)
            {
                Log.Error("获取全部可追踪参数:{0}", e, "SELECT * FROM ParamForTrace ");
            }
            return pts;
        }

        public static List<ParamForTrace> GetTracedParams()
        {
            IsDbExist();
            Log.Debug("获取全部可追踪的参数");
            var pts = new List<ParamForTrace>();
            try
            {
                var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
                con.Open();
                using SQLiteCommand cmd = con.CreateCommand();
                var reader = SqliteHelper.ExecuteReader(cmd, "SELECT * FROM ParamForTrace WHERE IsTrace=1", null);
                while (reader.Read())
                {
                    pts.Add(new ParamForTrace
                    {
                        DataNo = reader["DataNo"].ToInt32(),
                        IsTrace = 1
                    });
                }
                reader.Close();
            }
            catch (Exception e)
            {
                Log.Error("获取被追踪参数:{0}", e, "SELECT * FROM ParamForTrace WHERE IsTrace=1");
            }
            return pts;
        }
        /// <summary>
        /// 获取全部权限
        /// </summary>
        /// <returns></returns>
        public static List<Permission> GetAllPermissions()
        {
            IsDbExist();
            Log.Debug("获取全部权限");
            var sql = new StringBuilder("SELECT * FROM Permission WHERE (Type=0");
            BaseInfo bi = ConfigAndSettings.GetBaseInfo();
            int gmpValue;
            int datashieldValue;
            int unitValue;
            int pluginValue;
            if (bi.Gmp)
            {
                //sql.Append(" OR Type=1");
                gmpValue = 1;
            }
            else
            {
                gmpValue = -1;
            }
            sql.Append(" OR (Type-(").Append(gmpValue).Append("))=0");
            if (bi.DataShield)
            {
                datashieldValue = 2;
                //sql.Append(" OR Type=2");
            }
            else
            {
                datashieldValue = -2;
            }
            sql.Append(" OR (Type-(").Append(datashieldValue).Append("))=0");
            if (bi.Unit)
            {
                unitValue = 3;
                //sql.Append(" OR Type=3");
            }
            else
            {
                unitValue = -3;
            }
            sql.Append(" OR (Type-(").Append(unitValue).Append("))=0");
            if (bi.Plugin)
            {
                pluginValue = 4;
                //sql.Append(" OR Type=4");
            }
            else
            {
                pluginValue = -4;

            }
            sql.Append(" OR (Type-(").Append(pluginValue).Append("))=0");
            sql.Append(")");

            var permissions = new List<Permission>();
            try
            {
                var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
                con.Open();
                using SQLiteCommand cmd = con.CreateCommand();
                var reader = SqliteHelper.ExecuteReader(cmd, sql.ToString(), null);
                while (reader.Read())
                {
                    permissions.Add(new Permission
                    {
                        Id = reader["Id"].ToInt32(),
                        Code = reader["Code"].ToString(),
                        Type = reader["Type"].ToInt32(),
                        ParentId = reader["ParentId"].ToString(),
                        NameCn = reader["NameCn"].ToString(),
                        NameEn = reader["NameEn"].ToString()
                    });
                }
                reader.Close();
                foreach (Permission per in permissions)
                {
                    if (per.ParentId == "ParamSet")
                    {
                        //单个参数语言查询数据库模板
                        per.NameCn = CurrentRepository.GetLanguage(per.Code.ToInt32());
                    }
                    else
                    {
                        per.NameCn = LanguageManager.GetLanguageValue(per.NameCn);
                    }

                }
            }
            catch (Exception e)
            {
                Log.Error("获取全部权限:{0}", e, "SELECT * FROM Permission ");
            }
            return permissions;
        }

        /// <summary>
        /// 判断用户登录名是否存在
        /// </summary>
        /// <returns></returns>
        public static bool Exists(string roleName)
        {
            IsDbExist();
            var r = SqliteHelper.ExecuteScalar(DbFile, "SELECT COUNT(1) FROM Role WHERE Name=@Name", roleName);
            if (r == null) return false;
            return r.ToInt32() > 0;
        }

        /// <summary>
        /// 删除权限
        /// </summary>
        /// <param name="rid"></param>
        /// <returns></returns>
        public static int DeleteRole(int rid)
        {
            IsDbExist();
            Log.Debug("删除ID为{0}的角色", rid);
            var rc =
                SqliteHelper.ExecuteScalar(DbFile, "SELECT COUNT(1) FROM ViewUser WHERE Role=@Role AND IsDelete=0", new object[] { rid })
                    .ToInt32();
            if (rc > 0) 
               throw new BusinessException(LanguageManager.GetLanguageValue("SD-RoleIsUsed"));

            using var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
            con.Open();
            var tran = con.BeginTransaction();
            SqliteHelper.ExecuteNonQuery(tran, "DELETE FROM Role WHERE Id=@Id", rid);
            SqliteHelper.ExecuteNonQuery(tran, "DELETE FROM RolePermission WHERE Rid=@Rid", rid);
            try
            {
                tran.Commit();
                return 1;
            }
            catch (Exception e)
            {
                tran.Rollback();
                Log.Error("删除ID为{0}的角色失败", e, rid);
                return -1;
            }
        }
    }
}
